/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DAO.util.ConectaBanco;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import model.Login;
import model.Time;

/**
 *
 * @author Alberto
 */
public class TimeDAO {

    public boolean criar(Time time) {
        try {

            Connection conexao = ConectaBanco.getConexao();

            String sql = "insert into Times(nome_time, cidade_time, nome_responsavel, cpf_responsavel, email_responsavel, telefone_responsavel,comentario,categoria,cabeca,id_chave,id_login,status)values (?,?,?,?,?,?,?,?,0,0,?,?)";

            PreparedStatement pstmt = conexao.prepareStatement(sql);

            pstmt.setString(1, time.getNome_time());
            pstmt.setString(2, time.getCidade_time());
            pstmt.setString(3, time.getNome_responsavel());
            pstmt.setString(4, time.getCpf_responsavel());
            pstmt.setString(5, time.getEmail_responsavel());
            pstmt.setString(6, time.getTelefone_responsavel());
            pstmt.setString(7, time.getComentario());
            pstmt.setString(8, time.getCategoria());
            pstmt.setInt(9, time.getLogin().getId());
            pstmt.setInt(10, time.getStatus());

            pstmt.execute();
            conexao.close();

            return true;

        } catch (SQLException sql) {
            sql.printStackTrace();
            return false;

        }

    }

    public ArrayList<Time> buscarTodas(String nome) {
        ArrayList<Time> times = new ArrayList<Time>();
        Connection conexao = null;
        try {
            conexao = ConectaBanco.getConexao();
            String sql = "select * from Times where nome_time ilike ? and status = 1";

            PreparedStatement pst = conexao.prepareStatement(sql);

            pst.setString(1, nome + "%");
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                Time time = new Time();
                time.setNome_time(rs.getString("nomeTime"));
                time.setCidade_time(rs.getString("cidade_time"));
                time.setNome_responsavel(rs.getString("nomeResponsavel"));
                time.setCpf_responsavel(rs.getString("cpfResponsavel"));
                time.setEmail_responsavel(rs.getString("emailResponsavel"));
                time.setTelefone_responsavel(rs.getString("telefoneResponsavel"));
                time.setComentario(rs.getString("comentario"));
                time.setCategoria(rs.getString("categoria"));

                times.add(time);
            }

            conexao.close();
            return times;

        } catch (Exception sql) {
            System.out.println("Erro ao buscar um aluno" + sql.getMessage());
            return null;
        }

    }

    public ArrayList<Time> buscarPorSerie(String categoria) {
        ArrayList<Time> times = new ArrayList<Time>();
        Connection conexao = null;
        try {
            conexao = ConectaBanco.getConexao();
            String sql = "select * from Times where categoria ilike ? and status = 2 order by nome_time";

            PreparedStatement pst = conexao.prepareStatement(sql);

            pst.setString(1, categoria + "%");
      
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                Time time = new Time();
                time.setId_time(rs.getInt("id"));
                time.setNome_time(rs.getString("nome_time"));
                time.setCidade_time(rs.getString("cidade_time"));
                time.setNome_responsavel(rs.getString("nome_responsavel"));
                time.setCpf_responsavel(rs.getString("cpf_responsavel"));
                time.setEmail_responsavel(rs.getString("email_responsavel"));
                time.setTelefone_responsavel(rs.getString("telefone_responsavel"));
                time.setComentario(rs.getString("comentario"));
                time.setCategoria(rs.getString("categoria"));
                time.setStatus(rs.getInt("status"));
                time.setCabeca(rs.getInt("cabeca"));

                times.add(time);
            }

            conexao.close();
            return times;

        } catch (Exception sql) {
            System.out.println("Erro ao buscar um aluno" + sql.getMessage());
            return null;
        }

    }

    public ArrayList<Time> buscarTimePorChave(int id_chave) {
        ArrayList<Time> times = new ArrayList<Time>();
        Connection conexao = null;
        try {
            conexao = ConectaBanco.getConexao();
            String sql = "select * from Times where id_chave = ? order by cabeca desc ";

            PreparedStatement pst = conexao.prepareStatement(sql);

            pst.setInt(1, id_chave);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                Time time = new Time();
                time.setId_time(rs.getInt("id"));
                time.setNome_time(rs.getString("nome_time"));
                time.setCidade_time(rs.getString("cidade_time"));
                time.setNome_responsavel(rs.getString("nome_responsavel"));
                time.setCpf_responsavel(rs.getString("cpf_responsavel"));
                time.setEmail_responsavel(rs.getString("email_responsavel"));
                time.setTelefone_responsavel(rs.getString("telefone_responsavel"));
                time.setComentario(rs.getString("comentario"));
                time.setCategoria(rs.getString("categoria"));
                time.setStatus(rs.getInt("status"));
                time.setCabeca(rs.getInt("cabeca"));

                times.add(time);
            }

            conexao.close();
            return times;

        } catch (Exception sql) {
            System.out.println("Erro ao buscar um Time" + sql.getMessage());
            return null;
        }

    }

    public Time buscarPorId(int id) {

        Connection conexao = null;
        try {
            conexao = ConectaBanco.getConexao();
            String sql = "select * from times where id=?";

            PreparedStatement pst = conexao.prepareStatement(sql);

            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            Time time = new Time();
            while (rs.next()) {
                time.setId_time(rs.getInt("id"));
                time.setNome_time(rs.getString("nome_time"));
                time.setCidade_time(rs.getString("cidade_time"));
                time.setNome_responsavel(rs.getString("nome_responsavel"));
                time.setCpf_responsavel(rs.getString("cpf_responsavel"));
                time.setEmail_responsavel(rs.getString("email_responsavel"));
                time.setTelefone_responsavel(rs.getString("telefone_responsavel"));
                time.setComentario(rs.getString("comentario"));
                time.setCategoria(rs.getString("categoria"));
                time.setStatus(rs.getInt("status"));

            }
            conexao.close();
            return time;

        } catch (Exception sql) {
            System.out.println("Erro ao buscar o time" + sql.getMessage());
            return null;
        }

    }

    public Time buscarPorNome(String nome) {

        Connection conexao = null;
        try {
            conexao = ConectaBanco.getConexao();
            String sql = "select * from times where nome_time =?";

            PreparedStatement pst = conexao.prepareStatement(sql);

            pst.setString(1, nome );
            ResultSet rs = pst.executeQuery();
            Time time = new Time();
            while (rs.next()) {
                time.setId_time(rs.getInt("id"));
                time.setNome_time(rs.getString("nome_time"));
                time.setCidade_time(rs.getString("cidade_time"));
                time.setNome_responsavel(rs.getString("nome_responsavel"));
                time.setCpf_responsavel(rs.getString("cpf_responsavel"));
                time.setEmail_responsavel(rs.getString("email_responsavel"));
                time.setTelefone_responsavel(rs.getString("telefone_responsavel"));
                time.setComentario(rs.getString("comentario"));
                time.setCategoria(rs.getString("categoria"));
                time.setStatus(rs.getInt("status"));

            }
            conexao.close();
            return time;

        } catch (Exception sql) {
            System.out.println("Erro ao buscar o time" + sql.getMessage());
            return null;
        }

    }

    public Time buscarPorIdLogin(Login login) {

        Connection conexao = null;
        try {
            conexao = ConectaBanco.getConexao();
            String sql = "select * from times where id_login=?";

            PreparedStatement pst = conexao.prepareStatement(sql);

            LoginDAO loginDAO = new LoginDAO();
            pst.setInt(1, login.getId());
            ResultSet rs = pst.executeQuery();
            Time time = new Time();
            rs.next();
            time.setId_time(rs.getInt("id"));
            time.setNome_time(rs.getString("nome_time"));
            time.setCidade_time(rs.getString("cidade_time"));
            time.setLogin(loginDAO.buscarLoginByCodigo(rs.getInt("id_login")));
            time.setNome_responsavel(rs.getString("nome_responsavel"));
            time.setCpf_responsavel(rs.getString("cpf_responsavel"));
            time.setEmail_responsavel(rs.getString("email_responsavel"));
            time.setTelefone_responsavel(rs.getString("telefone_responsavel"));
            time.setComentario(rs.getString("comentario"));
            time.setCategoria(rs.getString("categoria"));

            time.setStatus(rs.getInt("status"));

            conexao.close();
            return time;

        } catch (Exception sql) {
            System.out.println("Erro ao buscar o time" + sql.getMessage());
            return null;
        }

    }

    public boolean validarLogin(Login login) throws SQLException {

        Connection conexao = null;
        try {
            conexao = ConectaBanco.getConexao();
            String sql = "select id from times where id_login=?";

            PreparedStatement pst = conexao.prepareStatement(sql);

            pst.setInt(1, login.getId());
            ResultSet rs = pst.executeQuery();

            int id = 0;
            while (rs.next()) {
                id = rs.getInt("id");
            }
            conexao.close();
            if (id != 0) {
                return true;
            } else {
                return false;
            }

        } catch (SQLException sql) {
            System.out.println("Erro ao buscar o time" + sql.getMessage());

            return false;
        }

    }

    public boolean atualizar(Time time) {
        try {

            Connection conexao = ConectaBanco.getConexao();
            String sql = "update Times set nome_time=?, cidade_time=?, nome_responsavel=?, cpf_responsavel=?, email_responsavel=?,  telefone_responsavel=?, comentario=?, categoria=?, where id=?";
            PreparedStatement pstmt = conexao.prepareCall(sql);
            pstmt.setString(1, time.getNome_time());
            pstmt.setString(2, time.getCidade_time());
            pstmt.setString(3, time.getNome_responsavel());
            pstmt.setString(4, time.getCpf_responsavel());
            pstmt.setString(5, time.getEmail_responsavel());
            pstmt.setString(6, time.getTelefone_responsavel());
            pstmt.setString(7, time.getComentario());
            pstmt.setString(8, time.getCategoria());
            pstmt.setInt(9, time.getId_time());
            pstmt.execute();
            conexao.close();

            return true;
        } catch (SQLException sql) {
            return false;

        }
    }

    public boolean apagar(int num) {

        try {

            Connection conexao = ConectaBanco.getConexao();

            String sql = "delete from Times where id = ?";
            PreparedStatement pstmt = conexao.prepareCall(sql);
            pstmt.setInt(1, num);

            pstmt.execute();

            conexao.close();
            return true;
        } catch (Exception ex) {
            return false;
        }

    }

    public boolean marcarInativo(int id) {
        try {
            Connection conexao = ConectaBanco.getConexao();

            String sql = "update Times set status=0 where id=?";

            PreparedStatement pstmt = conexao.prepareCall(sql);

            pstmt.setInt(1, id);

            pstmt.execute();

            conexao.close();
            return true;
        } catch (SQLException sql) {

            return false;

        }
    }

    public boolean marcarAtivo(int id) {
        try {
            Connection conexao = ConectaBanco.getConexao();

            String sql = "update Times set status=1 where id=?";

            PreparedStatement pstmt = conexao.prepareCall(sql);

            pstmt.setInt(1, id);

            pstmt.execute();

            conexao.close();
            return true;
        } catch (SQLException sql) {

            return false;

        }
    }

    public int contarTimesA() throws Exception {
        int timesA = 0;

        Connection conexao = ConectaBanco.getConexao();

        String sql = "select count(id) as cont from times where categoria ilike 'A'";

        PreparedStatement pst = conexao.prepareCall(sql);
        ResultSet rs = pst.executeQuery();

        rs.next();
        timesA = rs.getInt("cont");
        System.out.println("O número de times da categoria A é " + timesA);

        return timesA;
    }

    public int contarTimesB() throws Exception {
        int timesB = 0;

        Connection conexao = ConectaBanco.getConexao();

        String sql = "select count(id) as cont from times where categoria ilike 'B'";

        PreparedStatement pst = conexao.prepareCall(sql);
        ResultSet rs = pst.executeQuery();

        rs.next();
        timesB = rs.getInt("cont");
        System.out.println("O número de times da categoria B é " + timesB);

        return timesB;
    }

    public ArrayList<Time> sortearCabecaA() {
        ArrayList<Time> times = new ArrayList<Time>();

        Connection conexao = null;
        try {
            conexao = ConectaBanco.getConexao();
            String sql = "select id, nome_time from Times where categoria ilike 'A' and cabeca=1 and status=2";
            PreparedStatement pst = conexao.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                Time time = new Time();
                time.setId_time(rs.getInt("id"));
                time.setNome_time(rs.getString("nome_time"));
                times.add(time);
            }

            Collections.shuffle(times);
            conexao.close();

            return times;

        } catch (Exception sql) {
            System.out.println("Erro ao buscar um aluno" + sql.getMessage());
            return null;
        }
    }

    public ArrayList<Time> sortearCorpoA() {
        ArrayList<Time> times = new ArrayList<Time>();

        Connection conexao = null;
        try {
            conexao = ConectaBanco.getConexao();
            String sql = "select id, nome_time from Times where categoria ilike 'A' and cabeca=0 and status=2";
            PreparedStatement pst = conexao.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                Time time = new Time();
                time.setId_time(rs.getInt("id"));
                time.setNome_time(rs.getString("nome_time"));
                times.add(time);
            }

            Collections.shuffle(times);
            conexao.close();

            return times;

        } catch (Exception sql) {
            System.out.println("Erro ao buscar um aluno" + sql.getMessage());
            return null;
        }
    }

    public ArrayList<Time> sortearCabecaB() {
        ArrayList<Time> times = new ArrayList<Time>();

        Connection conexao = null;
        try {
            conexao = ConectaBanco.getConexao();
            String sql = "select id, nome_time from Times where categoria ilike 'B' and cabeca=1 and status=2";
            PreparedStatement pst = conexao.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                Time time = new Time();
                time.setId_time(rs.getInt("id"));
                time.setNome_time(rs.getString("nome_time"));
                times.add(time);
            }

            Collections.shuffle(times);
            conexao.close();

            return times;

        } catch (Exception sql) {
            System.out.println("Erro ao buscar um aluno" + sql.getMessage());
            return null;
        }
    }

    public ArrayList<Time> sortearCorpoB() {
        ArrayList<Time> times = new ArrayList<Time>();

        Connection conexao = null;
        try {
            conexao = ConectaBanco.getConexao();
            String sql = "select id, nome_time from Times where categoria ilike 'B' and cabeca=0 and status=2";
            PreparedStatement pst = conexao.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                Time time = new Time();
                time.setId_time(rs.getInt("id"));
                time.setNome_time(rs.getString("nome_time"));
                times.add(time);
            }

            Collections.shuffle(times);
            conexao.close();

            return times;

        } catch (Exception sql) {
            System.out.println("Erro ao buscar um aluno" + sql.getMessage());
            return null;
        }
    }

    public boolean setIdChave(Time time) {

        try {

            Connection conexao = ConectaBanco.getConexao();
            String sql = "update Times set id_chave=? where id=?";
            PreparedStatement pstmt = conexao.prepareCall(sql);

            pstmt.setInt(1, time.getId_chave());
            pstmt.setInt(2, time.getId_time());
            pstmt.execute();
            conexao.close();

            return true;
        } catch (SQLException sql) {
            return false;

        }
    }
    
        public ArrayList<Time> buscarTimesA() {
        ArrayList<Time> times = new ArrayList<Time>();
        Connection conexao = null;
        try {
            conexao = ConectaBanco.getConexao();
            String sql = "select * from Times where categoria ilike 'A' AND status=1 order by cabeca DESC";

            PreparedStatement pst = conexao.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                Time time = new Time();
                time.setId_time(rs.getInt("id"));
                time.setNome_time(rs.getString("nome_time"));
                time.setNome_responsavel(rs.getString("nome_responsavel"));
                time.setCategoria(rs.getString("categoria"));
                time.setCabeca(rs.getInt("cabeca"));
                times.add(time);
            }

            conexao.close();
            return times;

        } catch (Exception sql) {
            System.out.println("Erro ao buscar um aluno" + sql.getMessage());
            return null;
        }

    }
    
    public ArrayList<Time> buscarTimesB() {
        ArrayList<Time> times = new ArrayList<Time>();
        Connection conexao = null;
        try {
            conexao = ConectaBanco.getConexao();
            String sql = "select * from Times where categoria ilike 'B' AND status=1 order by cabeca DESC";

            PreparedStatement pst = conexao.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                Time time = new Time();
                time.setId_time(rs.getInt("id"));
                time.setNome_time(rs.getString("nome_time"));
                time.setNome_responsavel(rs.getString("nome_responsavel"));
                time.setCategoria(rs.getString("categoria"));
                time.setCabeca(rs.getInt("cabeca"));
                times.add(time);
            }

            conexao.close();
            return times;

        } catch (Exception sql) {
            System.out.println("Erro ao buscar um aluno" + sql.getMessage());
            return null;
        }

    }

public boolean atualizarDivisao(Time time) {
        try {

            Connection conexao = ConectaBanco.getConexao();
            String sql = "update Times set categoria=?,cabeca=? where id=?";
            PreparedStatement pstmt = conexao.prepareCall(sql);
            pstmt.setString(1, time.getCategoria());
            pstmt.setInt(2, time.getCabeca());
            pstmt.setInt(3, time.getId_time());
            pstmt.execute();
            conexao.close();

            return true;
        } catch (SQLException sql) {
            return false;

        }
    }


    public ArrayList<Time> buscarTimes() {
        ArrayList<Time> times = new ArrayList<Time>();
        Connection conexao = null;
        try {
            conexao = ConectaBanco.getConexao();
            String sql = "select * from Times order by status";

            PreparedStatement pst = conexao.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();

            while (rs.next()) {
                Time time = new Time();
                time.setId_time(rs.getInt("id"));
                time.setNome_time(rs.getString("nome_time"));
                time.setNome_responsavel(rs.getString("nome_responsavel"));
                time.setCategoria(rs.getString("categoria"));
                time.setStatus(rs.getInt("status"));
                times.add(time);
            }

            conexao.close();
            return times;

        } catch (Exception sql) {
            System.out.println("Erro ao buscar um aluno" + sql.getMessage());
            return null;
        }

    }
    
    public boolean atualizarPagamento(Time time) {
        try {

            Connection conexao = ConectaBanco.getConexao();
            String sql = "update Times set status=? where id=?";
            PreparedStatement pstmt = conexao.prepareCall(sql);
            pstmt.setInt(1, time.getStatus());
            pstmt.setInt(2, time.getId_time());
            pstmt.execute();
            conexao.close();

            return true;
        } catch (SQLException sql) {
            return false;

        }
    }
    
    
    
    
    public int contarTimesAAtivos() throws Exception {
        int timesA = 0;

        Connection conexao = ConectaBanco.getConexao();

        String sql = "select count(id) as cont from times where categoria ilike 'A' AND status=1";

        PreparedStatement pst = conexao.prepareCall(sql);
        ResultSet rs = pst.executeQuery();

        rs.next();
        timesA = rs.getInt("cont");
        System.out.println("O número de times da categoria A é " + timesA);

        return timesA;
    }
    
    public int contarCabecaTimesA() throws Exception {
        int timesA = 0;

        Connection conexao = ConectaBanco.getConexao();

        String sql = "select count(id) as cont from times where categoria ilike 'A' AND status =1 AND cabeca=1";

        PreparedStatement pst = conexao.prepareCall(sql);
        ResultSet rs = pst.executeQuery();

        rs.next();
        timesA = rs.getInt("cont");
        System.out.println("O número de cabeças de chave no times da categoria A é " + timesA);

        return timesA;
    }

        
    public int contarTimesBAtivos() throws Exception {
        int timesB = 0;

        Connection conexao = ConectaBanco.getConexao();

        String sql = "select count(id) as cont from times where categoria ilike 'B' AND status=1";

        PreparedStatement pst = conexao.prepareCall(sql);
        ResultSet rs = pst.executeQuery();

        rs.next();
        timesB = rs.getInt("cont");
        System.out.println("O número de times da categoria B é " + timesB);

        return timesB;
    }
    
    public int contarCabecaTimesB() throws Exception {
        int timesB = 0;

        Connection conexao = ConectaBanco.getConexao();

        String sql = "select count(id) as cont from times where categoria ilike 'B' AND status =1 AND cabeca=1";

        PreparedStatement pst = conexao.prepareCall(sql);
        ResultSet rs = pst.executeQuery();

        rs.next();
        timesB = rs.getInt("cont");
        System.out.println("O número de times da categoria B é " + timesB);

        return timesB;
    }

}



